package com.topisv.tms.tools;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import org.apache.commons.io.IOUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.topisv.tms.entity.dto.ExcelDto;
import com.topisv.tms.utils.ColorUtil;

/**
 * Class: exportUtil.java Description: 导出工具类
 * @author song
 */
public class ExcelUtils2 {
	private final static Logger logger = LoggerFactory.getLogger(ExcelUtils.class);
	public final static String TEMPPATH="D:\\myLogs\\";
	
	private static WritableWorkbook wwb;
	
	/**
	 * 创建WorkBook
	 * @param os
	 * @return workbook
	 */
	public static void getInstance(OutputStream os){
		try {
				wwb = Workbook.createWorkbook(os);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * Excel表格添加Sheet页
	 * @param wwb
	 * @param sheet
	 * @param sheetname
	 * @param sheetNumber
	 */
	public static WritableSheet addSheet(String sheetname,int sheetNumber){
		return wwb.createSheet(sheetname, sheetNumber);
	}
	
	/**
	 * 关闭workbook
	 */
	public static void closeWorkBook(){
		try {
			wwb.write();
			wwb.close();
		} catch (WriteException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 不包含标题的sheet
	 * @param title
	 * @param sheetname
	 * @param list
	 */
	public static void writeSheet(WritableSheet sheet,String[] title, List<Object[]> list) {
		writeSheet(sheet,title, null, list);
	}
	
	/**
	 * 包含标题的sheet
	 * @param title
	 * @param sheetname
	 * @param list
	 */
	public static void writeSheet(WritableSheet sheet,String[] title,String title_page, List<Object[]> list) {
		try {
			//页标题行
			WritableFont font_pageTitle = new WritableFont(WritableFont
					.createFont("仿宋_GB2312"),// 字体
					13, // 字号
					WritableFont.NO_BOLD, // 粗体
					false, // 斜体
					UnderlineStyle.NO_UNDERLINE, // 下划线
					Colour.BLACK, // 字体颜色
					ScriptStyle.NORMAL_SCRIPT);
			int startRow = 0;
			if(!StringUtils.isBlank(title_page)){
				WritableCellFormat fromat_pageTitle = new WritableCellFormat(font_pageTitle);
				fromat_pageTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
				fromat_pageTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
				
				sheet.addCell(new Label(0, 0, title_page,fromat_pageTitle)); // 合并单元格
				sheet.mergeCells(0, 0 , title.length-1, 0); // 合并单元格
				startRow++;
			}
			sheet.setRowView(0, 800);  // 设置行的高度
			
			//栏目标题行
			WritableFont font_columnTitle = new WritableFont(WritableFont
					.createFont("仿宋_GB2312"),// 字体
					WritableFont.DEFAULT_POINT_SIZE, // 字号
					WritableFont.BOLD, // 粗体
					false, // 斜体
					UnderlineStyle.NO_UNDERLINE, // 下划线
					Colour.BLACK, // 字体颜色
					ScriptStyle.NORMAL_SCRIPT);
			
			WritableCellFormat fromat_columnTitle = new WritableCellFormat(font_columnTitle);
			fromat_columnTitle.setAlignment(Alignment.CENTRE); // 设置文字水平居中
			fromat_columnTitle.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
			fromat_columnTitle.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
			fromat_columnTitle.setBackground(ColorUtil.getNearestColour("#66CCFF")); // 设置单元格背景颜色
			
			for (int i = 0; i < title.length; i++) {
				Label label = new Label(i, startRow, title[i], fromat_columnTitle);
				sheet.addCell(label);
				
				sheet.setColumnView(i, 15); // 设置列的宽度
				sheet.setRowView(1, 400); // 设置行的高度
			}
			//表格数据行
			
			//单元格格式1——正常字体
			WritableCellFormat format_cell_1 = new WritableCellFormat();
			format_cell_1.setAlignment(Alignment.CENTRE); // 设置文字水平居中
			format_cell_1.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
			format_cell_1.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线   
		    
		    
		    //单元格格式2 ——红色背景
			WritableCellFormat format_cell_2 = new WritableCellFormat();
			format_cell_2.setAlignment(Alignment.CENTRE); // 设置文字水平居中
			format_cell_2.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置文字垂直居中
			format_cell_2.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线   
			format_cell_2.setBackground(Colour.RED);
			   
			if (list != null && list.size() > 0) {
				
				for (int i = 0; i < list.size(); i++) {
					
					Object[] obj = (Object[]) list.get(i);
					for (int j = 0; j < obj.length-1; j++) {
						String value= (obj[j]==null?"":obj[j].toString());
						Label label=null;
						if(obj[obj.length-1]!=null){
							label= new Label(j, i + startRow + 1, value, format_cell_2);
						}else{
							label = new Label(j, i + startRow + 1, value, format_cell_1);
						}
						sheet.addCell(label);
					}
					sheet.setRowView(i+2, 400); // 设置行的高度
				}
			}
			
		} catch (Exception e) {
			logger.error("ExcelUtils2 writeExcel case some error.",e);
		}
	}
}
